#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2023/12/4 13:34
# @Author : 闫峰
# @File    : listing_filter_9058.py
import datetime
import json
from decimal import Decimal

import requests

from common.setting import ensure_path_sep
from utils.mysqlUtils.db_pool import Database
import os
import threading
import time
from common.setting import ensure_path_sep
from utils.logging_tool.log_control import LogHandler
from utils.mysqlUtils.db_pool import Database
from utils.mysqlUtils.mongodb_utils import MongoDBLink

current_path = os.path.abspath(__file__)
current_dir = os.path.dirname(current_path)
path_list = current_path.split("\\")
dis_path = "\\".join(path_list[-2:-1])


def remove_file(info_path):
    if os.path.exists(info_path):
        os.remove(info_path)
    else:
        print(f"{info_path} 不存在")


info_path = ensure_path_sep(f"\\logs\\info-{dis_path}{time.time()}")
error_path = ensure_path_sep(f"\\logs\\\error-{dis_path}{time.time()}.log")
warning_path = ensure_path_sep(f"\\logs\\warning-{dis_path}{time.time()}.log")
remove_file(info_path)
remove_file(error_path)
remove_file(warning_path)
INFO = LogHandler(info_path, level='info')
ERROR = LogHandler(error_path, level='error')
WARNING = LogHandler(warning_path, level='warning')


class SkuFilter:
    """
    allegro listing 分级策略
    涉及表 t_allegro_listing_
    """
    db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
    # 定义mongodb
    modb = MongoDBLink(
        uri='mongodb://admin:Zhcx?2021@10.158.158.29:27017/?authSource=admin',
        dbname='test_zhcxkj_java_center', coll_name='t_sku_strategy_relation')

    def __init__(self, strategy_id):
        # 查询策略
        self.strategy_id = strategy_id
        get_stragety = """
        				SELECT `status`,sku_template_id,listing_template_id
				FROM test_zhcxkj_center_strategy_comprehensive.t_allegro_marketing_strategy
				WHERE id= %s
				AND is_deleted=0 
        """
        stragety = self.db.fetch_all_tuple(get_stragety, (strategy_id,))
        if not len(stragety):
            raise
        if stragety[0][0]:
            raise
        self.sku_template_id = stragety[0][1]
        self.listing_template_id = stragety[0][2]

        # 查询策略条件
        get_strategy_conditions = """
        SELECT  a.`status`, -- 模板状态
        GROUP_CONCAT(b.category_id), -- 分类id
        a.listing_publish_start_time, -- listing上架开始时间
        a.listing_publish_end_time, -- listing上架结束时间
        a.price_min, -- 平台价格最小值
        a.price_max, -- 平台价格最大值
        a.listing_sale_min, -- listing销量最小值
        a.listing_sale_max, -- listing销量最大值
        a.listing_sale_recent_days, -- listing最近几天的销量数据
        a.sku_sale_min, -- sku销量最小值
        a.sku_sale_max, -- sku销量最大值
        a.sku_sale_recent_days, -- sku最近几天的销量数据，最大值90天
        a.in_promotion,  -- 促销状态，0：未参与促销折扣，1：已参与促销折扣
        a.listing_discount_min,  -- lisitng促销折扣最小值
        a.listing_discount_max,  -- lisitng促销折扣最大值
        a.listing_discount_type  -- listing折扣值类型，0：同品折扣，1：不同品折扣集合
        FROM test_zhcxkj_center_strategy_comprehensive.t_allegro_listing_filter_template a
        LEFT JOIN test_zhcxkj_center_strategy_comprehensive.t_allegro_listing_filter_category b ON a.id=b.listing_template_id
        WHERE a.id= %s
        """
        strategy_conditions = self.db.fetch_all_tuple(get_strategy_conditions, (stragety[0][2],))
        if not len(strategy_conditions):
            raise
        if not strategy_conditions[0][0]:
            raise
        self.strategy_conditions = strategy_conditions

    def sku_filter(self, source_id):
        num = source_id % 8 + 1
        # 分批次拉取数据源 在线的listing数据

        sql1 = f"""
        SELECT id,category_id,market_create_time,price,sku,offer_id
        FROM `test_zhcxkj_center_listing_comprehensive`.`t_allegro_listing_{num}` 
        WHERE id >%s
        and source_id=%s
        and status = 3
        and is_deleted=0
        and offer_id = 14290810153
        order by id
        limit 1000
        """
        id_index = 0
        offer_id_total = set()
        while True:
            data_list = self.db.fetch_all_tuple(sql1, (id_index, source_id))
            if not len(data_list):
                break
            id_index = data_list[-1][0]
            data_set = set(data_list)

            if not data_set:
                continue
            # 平台分类
            if self.strategy_conditions[0][1]:
                origin_data_set = data_set
                data_set = set(filter(lambda x: str(x[1]) in self.strategy_conditions[0][1], data_set))
                INFO.logger.info(f"分类id条件过滤了{list(map(lambda x: x[0], origin_data_set - data_set))}")
            if not data_set:
                continue
            # 上架时间
            if self.strategy_conditions[0][2]:
                origin_data_set = data_set
                data_set = set(
                    filter(lambda x: self.strategy_conditions[0][2] <= x[2] <= self.strategy_conditions[0][3],
                           data_set))
                INFO.logger.info(f"刊登时间条件过滤了{list(map(lambda x: x[0], origin_data_set - data_set))}")
            if not data_set:
                continue
            # 价格
            if any([self.strategy_conditions[0][4], self.strategy_conditions[0][5]]):
                origin_data_set = data_set
                if all([self.strategy_conditions[0][4], self.strategy_conditions[0][5]]):
                    data_set = set(
                        filter(lambda x: self.strategy_conditions[0][4] <= x[3] <= self.strategy_conditions[0][5],
                               data_set))
                    INFO.logger.info(f"价格条件过滤了{list(map(lambda x: x[0], origin_data_set - data_set))}")
                elif all([self.strategy_conditions[0][4], not self.strategy_conditions[0][5]]):
                    data_set = set(
                        filter(lambda x: self.strategy_conditions[0][4] <= x[3], data_set))
                    INFO.logger.info(f"价格条件过滤了{list(map(lambda x: x[0], origin_data_set - data_set))}")
                elif all([not self.strategy_conditions[0][4], self.strategy_conditions[0][5]]):
                    data_set = set(
                        filter(lambda x: x[3] <= self.strategy_conditions[0][5], data_set))
                    INFO.logger.info(f"价格条件过滤了{list(map(lambda x: x[0], origin_data_set - data_set))}")
            if not data_set:
                continue
            # sku销量
            if any([self.strategy_conditions[0][9], self.strategy_conditions[0][10]]):
                data_set = self.sku_sales_filter(data_set)
            if not data_set:
                continue
            # 平台listing销量
            if any([self.strategy_conditions[0][6], self.strategy_conditions[0][7]]):
                data_set = self.platform_sku_sales_filter(data_set)
            if not data_set:
                continue
            # 促销活动 过滤掉参加促销活动的数据  必选的
            data_set = self.filter_is_promotiom(data_set, num, self.strategy_conditions[0][12])
            if not data_set:
                continue

            # # listing促销折扣值
            # 判断促销折扣值 是否勾选 如果勾选  判断listing_discount_type = -1 （全部）0：同品折扣，1：不同品折扣集合
            """
            # 判断促销折扣值 是否勾选 如果勾选  判断listing_discount_type = -1 （全部）0：同品折扣，1：不同品折扣集合
            如果时全部 筛选出参加活动的offer  percentage 符合策略
            如果是 1 筛选出的参加 不同品折扣集合活动 的offer  percentage 符合策略  同品折扣 不用过滤
            如果是 0 筛选出的参加 同品折扣活动 的offer  percentage 符合策略  不同品折扣集合 不用过滤
            """
            if any([self.strategy_conditions[0][13], self.strategy_conditions[0][14]]):
                if self.strategy_conditions[0][15] == -1:
                    data_set = self.filter_promotion_discount_value_all(data_set, num)
                else:
                    data_set = self.filter_promotion_discount_value_by_promotion_type(data_set, num, self.strategy_conditions[0][15])

            if not data_set:
                continue
            offer_id_total |= set(map(lambda x: x[5], data_set))

        if not offer_id_total:
            ERROR.logger.error(f"最终过滤结果数为0")
            return
        ERROR.logger.error(f"最终过滤结果数为{len(offer_id_total)}")
        get_actual_offer_id_list = """
        				 SELECT offer_id
				 FROM test_zhcxkj_center_strategy_comprehensive.t_allegro_listing_filter_template_execute_detail a
				 WHERE listing_template_id=%s
				 AND source_id= %s
        """
        actual_offer_id_list = self.db.fetch_all_tuple(get_actual_offer_id_list, (self.listing_template_id, source_id))
        actual_offer_id_set = set(map(lambda x: x[0], actual_offer_id_list))
        if offer_id_total == actual_offer_id_set:
            pass
        else:
            print(offer_id_total - actual_offer_id_set)
            print()

    def filter_is_promotiom(self, data_set5, num, in_promotion=0):
        listing_id_set = set(map(lambda x: x[0], data_set5))

        # 查询得到正在参加的listingid
        get_listing_id_list = f"""
				SELECT a.id,b.id,c.id
				FROM test_zhcxkj_center_listing_comprehensive.t_allegro_listing_{num} a 
				JOIN test_zhcxkj_center_promotion.t_allegro_promotion_detail b on a.source_id=b.source_id AND a.offer_id=b.offer_id
                JOIN test_zhcxkj_center_promotion.t_allegro_promotion c ON c.promotion_id=b.promotion_id
				WHERE a.id in %s
				and a.is_deleted=0
				AND c.`status` in (1,2)
        """

        # 通过listing_id_set 得到 data_set
        get_data_set = f"""
                SELECT id,category_id,market_create_time,price,sku,offer_id
        FROM `test_zhcxkj_center_listing_comprehensive`.`t_allegro_listing_{num}` 
        WHERE id in %s
        """
        listing_id_list = self.db.fetch_all_tuple(get_listing_id_list, (listing_id_set,))
        listing_id_set_participate_activities = set(map(lambda x: x[0], listing_id_list))
        if in_promotion:
            INFO.logger.info(f"促销活动条件过过滤了 {listing_id_set - listing_id_set_participate_activities}")
            if not listing_id_set_participate_activities:
                return None
            data_list = self.db.fetch_all_tuple(get_data_set, (listing_id_set_participate_activities,))
            if not len(data_list):
                return None
            return set(data_list)

        else:
            INFO.logger.info(f"促销活动条件过过滤了 {listing_id_set_participate_activities}")
            if not listing_id_set - listing_id_set_participate_activities:
                return None
            data_list = self.db.fetch_all_tuple(get_data_set, (listing_id_set - listing_id_set_participate_activities,))
            if not len(data_list):
                return None
            return set(data_list)

    def sku_sales_filter(self, data_set):
        oringin_data_set = data_set
        sku_list = list(map(lambda x: x[4], data_set))
        #   根据策略规则获取销量
        if not self.strategy_conditions[0][11]:
            sku_sale_recent_days = None
        else:
            sku_sale_recent_days = self.strategy_conditions[0][11]

        # 得到sku 销量字典
        sales_dict = self.get_platform_sales(sku_list, "all", sku_sale_recent_days)
        # 匹配策略
        if all([self.strategy_conditions[0][9], not self.strategy_conditions[0][10]]):
            sales_dict = set(filter(lambda x: self.strategy_conditions[0][9] <= x[1], sales_dict.items()))

        elif all([not self.strategy_conditions[0][9], self.strategy_conditions[0][10]]):
            sales_dict = set(filter(lambda x: x[1] <= self.strategy_conditions[0][10], sales_dict.items()))

        elif all([self.strategy_conditions[0][9], self.strategy_conditions[0][10]]):
            sales_dict = set(filter(lambda x: self.strategy_conditions[0][9] <= x[1] <= self.strategy_conditions[0][10],
                                    sales_dict.items()))

        # 得到符合策略的sku列表
        sku_list = list(map(lambda x: x[0], sales_dict))
        # 得到过滤后的集合
        data_set = set(filter(lambda x: x[4] in sku_list, oringin_data_set))

        INFO.logger.info(f"SKU销量过滤了{set(map(lambda x: x[4], oringin_data_set - data_set))}")

        return data_set

    def platform_sku_sales_filter(self, data_set):
        oringin_data_set = data_set
        sku_list = list(map(lambda x: x[4], data_set))
        #   根据策略规则获取销量
        if not self.strategy_conditions[0][8]:
            sku_sale_recent_days = None
        else:
            sku_sale_recent_days = self.strategy_conditions[0][8]
        # 得到sku 销量字典
        sales_dict = self.get_platform_sales(sku_list, 67, sku_sale_recent_days)
        # 匹配策略
        if all([self.strategy_conditions[0][6], self.strategy_conditions[0][7]]):
            sales_dict = set(filter(lambda x: self.strategy_conditions[0][6] <= x[1] <= self.strategy_conditions[0][7],
                                    sales_dict.items()))

        elif all([not self.strategy_conditions[0][6], self.strategy_conditions[0][7]]):
            sales_dict = set(filter(lambda x: x[1] <= self.strategy_conditions[0][7], sales_dict.items()))

        elif all([self.strategy_conditions[0][6], not self.strategy_conditions[0][7]]):
            sales_dict = set(filter(lambda x: self.strategy_conditions[0][6] <= x[1], sales_dict.items()))

        # 得到符合策略的sku列表
        sku_list = list(map(lambda x: x[0], sales_dict))
        # 得到过滤后的集合
        data_set = set(filter(lambda x: x[4] in sku_list, oringin_data_set))
        INFO.logger.info(f"平台listing销量过滤了{set(map(lambda x: x[4], oringin_data_set - data_set))}")
        return data_set

    def get_platform_sales(self, sku_list: list[str], orderSourceType, sku_sales_days):
        """
        得到各个平台销量
        """
        sku_list = list(set(sku_list))
        url = """https://api-dw.zhcxkj.com/dw/erp/salesvolume/platform/salessku"""
        if orderSourceType != "all":
            param = {
                "input": {
                    "customerId": 1,
                    "orderSourceType": orderSourceType,
                    "skus": sku_list,
                    "days": sku_sales_days
                }
            }
        else:
            param = {
                "input": {
                    "customerId": 1,
                    "orderSourceType": None,
                    "skus": sku_list,
                    "days": sku_sales_days
                }
            }
        hearders = {
            "Content-Type": "application/json",
            "Authorization": "Basic aXJvYm90Ym94OmkxcjVvOEJvdGJveA==",
        }

        res = requests.post(url=url, headers=hearders, json=param)
        sales = {}
        if isinstance(orderSourceType, int):
            for data in res.json().get('data'):
                if data.get('orderSourceType') == orderSourceType:
                    if data["sku"] not in sales:
                        sales[data["sku"]] = data["salesVolume"]
                    else:
                        sales[data["sku"]] += data["salesVolume"]
            return sales
        else:
            for data in res.json().get('data'):
                if data["sku"] not in sales:
                    sales[data["sku"]] = data["salesVolume"]
                else:
                    sales[data["sku"]] += data["salesVolume"]
            return sales

    def filter_percentage(self):
        pass

    def filter_promotion_discount_value_all(self, data_set, num):
        # data_set 转化为 offer_id_set 集合
        origin_offer_id_set = set(map(lambda x: x[5], data_set))
        sql = """
        SELECT b.offer_id,a.percentage,a.promotion_type
        FROM test_zhcxkj_center_promotion.t_allegro_promotion a
        LEFT JOIN  test_zhcxkj_center_promotion.t_allegro_promotion_detail b ON a.promotion_id=b.promotion_id
        WHERE b.offer_id in %s
        """
        res = self.db.fetch_all_tuple(sql, (origin_offer_id_set,))
        # 过滤掉不符合的数据
        percentage_min = self.strategy_conditions[0][13]
        percentage_max = self.strategy_conditions[0][14]
        if percentage_min:
            percentage_min  = Decimal(str(percentage_min))
        if percentage_max:
            percentage_max = Decimal(str(percentage_max))
        if all([percentage_min,percentage_max]):
            # self.strategy_conditions[0][13] 由 int类型转化为 decimal类型
            res = set(filter(lambda x: percentage_min <= x[1] <= percentage_max, res))
        elif all([not percentage_min,percentage_max]):
            res = set(filter(lambda x: x[1] <= percentage_max, res))
        if all([percentage_min, not percentage_max]):
            res = set(filter(lambda x: percentage_min <= x[1], res))
        # 转化成offer_id_set
        after_filter_offer_id_set = set(map(lambda x: x[0], res))
        if not after_filter_offer_id_set:
            return None
        # 还原
        get_data_set = f"""
                SELECT id,category_id,market_create_time,price,sku,offer_id
        FROM `test_zhcxkj_center_listing_comprehensive`.`t_allegro_listing_{num}` 
        WHERE offer_id in %s
        """
        listing_id_list = self.db.fetch_all_tuple(get_data_set, (after_filter_offer_id_set,))
        return set(listing_id_list)

    def filter_promotion_discount_value_by_promotion_type(self, data_set, num, promotion_type):
        # data_set 转化为 offer_id_set 集合
        origin_offer_id_set = set(map(lambda x: x[5], data_set))
        after_filter_offer_id_set = None
        # 查询出不符合的同品listing
        if all([self.strategy_conditions[0][13], self.strategy_conditions[0][14]]):
            sql1 = """
            SELECT b.offer_id
            FROM test_zhcxkj_center_promotion.t_allegro_promotion a
            LEFT JOIN  test_zhcxkj_center_promotion.t_allegro_promotion_detail b ON a.promotion_id=b.promotion_id
            WHERE b.offer_id in %s
            and a.percentage < %s
            and a.percentage > %s
            and a.promotion_type= %s
            """
            res1 = self.db.fetch_all_tuple(sql1, (
            origin_offer_id_set, self.strategy_conditions[0][13], self.strategy_conditions[0][14], promotion_type))
            filter_offer_id_set = set(map(lambda x:x[0],res1))
            after_filter_offer_id_set = origin_offer_id_set - filter_offer_id_set
        elif all([not self.strategy_conditions[0][13], self.strategy_conditions[0][14]]):

            sql2 = """
            SELECT b.offer_id
            FROM test_zhcxkj_center_promotion.t_allegro_promotion a
            LEFT JOIN  test_zhcxkj_center_promotion.t_allegro_promotion_detail b ON a.promotion_id=b.promotion_id
            WHERE b.offer_id in %s
            and a.percentage > %s
            and a.promotion_type = %s
            """
            res2 = self.db.fetch_all_tuple(sql2, (
            origin_offer_id_set, self.strategy_conditions[0][14], promotion_type))
            filter_offer_id_set = set(map(lambda x:x[0],res2))
            after_filter_offer_id_set = origin_offer_id_set - filter_offer_id_set

        elif all([self.strategy_conditions[0][13], not self.strategy_conditions[0][14]]):

            sql2 = """
            SELECT b.offer_id
            FROM test_zhcxkj_center_promotion.t_allegro_promotion a
            LEFT JOIN  test_zhcxkj_center_promotion.t_allegro_promotion_detail b ON a.promotion_id=b.promotion_id
            WHERE b.offer_id in %s
            and a.percentage < %s
            and a.promotion_type = %s
            """
            res2 = self.db.fetch_all_tuple(sql2, (
                origin_offer_id_set, self.strategy_conditions[0][13],promotion_type))
            filter_offer_id_set = set(map(lambda x: x[0], res2))
            after_filter_offer_id_set = origin_offer_id_set - filter_offer_id_set
        if not after_filter_offer_id_set:
            return None
        # 还原
        get_data_set = f"""
                SELECT id,category_id,market_create_time,price,sku,offer_id
        FROM `test_zhcxkj_center_listing_comprehensive`.`t_allegro_listing_{num}` 
        WHERE offer_id in %s
        """
        listing_id_list = self.db.fetch_all_tuple(get_data_set, (after_filter_offer_id_set,))
        return set(listing_id_list)


if __name__ == '__main__':
    SkuFilter(37).sku_filter(9058)
    # SkuFilter().get_platform_sales("16089737",67,None)
    # print(SkuFilter(11).get_platform_sales(["17124896"], None, None))
    # orderSourceType_list = [2, 57]
    # sku = "10003056"
    # print(SkuFilter().first_time_filter("10001240"))
